package com.sinosoft.product;

import java.math.BigDecimal;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.sinosoft.base.GenericDaoHibernate;
import com.sinosoft.util.DateTimeUtils;

@Repository
public class DataQueryDaoHibernate extends
		GenericDaoHibernate<ZywxImage0, Long> implements DataQueryDao {

	public DataQueryDaoHibernate() {
		super(ZywxImage0.class);
		// TODO Auto-generated constructor stub
	}

	private ArrayList<ZywxImageAll> alist = null;
	private ArrayList<DataQueryModel> nationallist = null;
	private ZywxImageAll image = null;

	private String pagesize;
	private StringBuffer productStr;
	private String polygonList;

	@SuppressWarnings("unchecked")
	public ArrayList<ZywxImageAll> getDataQuery(final String sql) {
		// TODO Auto-generated method stub
		alist = new ArrayList();
		this.getSessionFactory().getCurrentSession().doWork(new Work() {
			ResultSet rs = null;
			Statement stmt = null;

			public void execute(Connection connection) throws SQLException {
				// TODO Auto-generated method stub
				stmt = connection.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				System.out.println(DateTimeUtils.getNowStrTime()
						+ "-getDataQuery-" + sql);
				rs = stmt.executeQuery(sql);
				while (rs != null && rs.next()) {
					String sceneId = rs.getString("PRODUCTID");
					String satelliteid = rs.getString("SATELLITEID");
					String sensorid = rs.getString("SENSORID");

					String scenedate = DateTimeUtils.DateToString(rs
							.getDate("scenedate"),
							DateTimeUtils.JAVA_TIME_FORMATTER);
					String receive = rs.getString("receive");
					String centerlat = rs.getString("centerlat");
					String centerlong = rs.getString("centerlong");

					String lefttlong = rs.getString("LEFTTLONG");
					String lefttlat = rs.getString("LEFTTLAT");

					String righttlong = rs.getString("RIGHTTLONG");
					String righttlat = rs.getString("RIGHTTLAT");

					String rightblong = rs.getString("RIGHTBLONG");
					String rightblat = rs.getString("RIGHTBLAT");

					String leftblong = rs.getString("LEFTBLONG");
					String leftblat = rs.getString("LEFTBLAT");

					String browsefile = rs.getString("BROWSEFILE");

					ZywxImageAll data = new ZywxImageAll();
					data
							.setProductid(BigDecimal.valueOf(Long
									.valueOf(sceneId)));
					data.setSatelliteid(satelliteid);
					data.setSensorid(sensorid);

					data.setScenedate(scenedate);
					data.setReceive(receive);
					data.setCenterlatitude(centerlat);
					data.setCenterlongitude(centerlong);

					data.setBottomrightlatitude(Double.valueOf(rightblat));
					data.setTopleftlongitude(Double.valueOf(lefttlong));

					data.setTopleftlatitude(Double.valueOf(lefttlat));
					data.setBottomrightlongitude(Double.valueOf(rightblong));

					data.setToprightlongitude(Double.valueOf(righttlong));
					data.setToprightlatitude(Double.valueOf(righttlat));

					data.setBottomleftlongitude(Double.valueOf(leftblong));
					data.setBottomleftlatitude(Double.valueOf(leftblat));
					data.setBrowsefilelocation(browsefile);

					alist.add(data);
				}

			}

		});
		return alist;
	}

	public String getAllPageSize(final String sql) {
		// TODO Auto-generated method stub
		pagesize = "";
		this.getSessionFactory().getCurrentSession().doWork(new Work() {
			ResultSet rs = null;
			Statement stmt = null;

			public void execute(Connection connection) throws SQLException {
				// TODO Auto-generated method stub
				stmt = connection.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				System.out.println(DateTimeUtils.getNowStrTime()
						+ "-getAllPageSize-" + sql);
				rs = stmt.executeQuery(sql);
				while (rs != null && rs.next()) {
					pagesize = rs.getString("PAGESIZE");
				}

			}

		});
		return pagesize;
	}

	public ZywxImageAll getDetails(final String sql, final String level) {
		// TODO Auto-generated method stub
		image = new ZywxImageAll();
		this.getSessionFactory().getCurrentSession().doWork(new Work() {
			ResultSet rs = null;
			Statement stmt = null;

			public void execute(Connection connection) throws SQLException {
				// TODO Auto-generated method stub
				stmt = connection.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				System.out.println(DateTimeUtils.getNowStrTime()
						+ "-getDetails-" + sql);
				rs = stmt.executeQuery(sql);
				if (rs.next()) {

					String productid = ""; // ("PRODUCTID");
					String sceneid = ""; // ("SCENEID");
					String productdate = ""; // ("PRODUCTDATE");
					String mapprojection = ""; // ("MAPPROJECTION");
					String producttype = ""; // ("PRODUCTTYPE");
					String earthmodel = ""; // ("EARTHMODEL");
					String sensorid = ""; // ("SENSORID");
					String satelliteid = ""; // ("SATELLITEID");
					String receivestationid = ""; // ("RECEIVESTATIONID");
					String receivetime = ""; // ("RECEIVETIME");
					String orbittype = ""; // ("ORBITTYPE");
					String atttype = ""; // ("ATTTYPE");
					String stripid = ""; // ("STRIPID");
					String producetype = ""; // ("PRODUCETYPE");
					String productlevel = ""; // ("PRODUCTLEVEL");
					String productformat = ""; // ("PRODUCTFORMAT");
					String scenedate = ""; // ("SCENEDATE");
					String bandnumber = ""; // ("BANDNUMBER");
					String scenepath = ""; // ("SCENEPATH");
					String scenerow = ""; // ("SCENEROW");
					String satpath = ""; // ("SATPATH");
					String satrow = ""; // ("SATROW");
					String scenecount = ""; // ("SCENECOUNT");
					String sceneshift = ""; // ("SCENESHIFT");
					String starttime = ""; // ("STARTTIME");
					String endtime = ""; // ("ENDTIME");
					String centertime = ""; // ("CENTERTIME");
					String startline = ""; // ("STARTLINE");
					String endline = ""; // ("ENDLINE");
					String imagegsd = ""; // ("IMAGEGSD");
					String widthinpixels = ""; // ("WIDTHINPIXELS");
					String heightinpixels = ""; // ("HEIGHTINPIXELS");
					String widthinmeters = ""; // ("WIDTHINMETERS");
					String heightinmeters = ""; // ("HEIGHTINMETERS");
					String regionname = ""; // ("REGIONNAME");
					String cloudpercent = ""; // ("CLOUDPERCENT");
					String datasize = ""; // ("DATASIZE");
					String rollviewingangle = ""; // ("ROLLVIEWINGANGLE");
					String pitchviewingangle = "";
					String rollsatelliteangle = "";
					String pitchsatelliteangle = "";
					String yawsatelliteangle = "";
					String solarazimuth = ""; // ("SOLARAZIMUTH");
					String solarzenith = ""; // ("SOLARZENITH");
					String satelliteazimuth = ""; // ("SATELLITEAZIMUTH");
					String satellitezenith = ""; // ("SATELLITEZENITH");
					String gainmode = ""; // ("GAINMODE");
					String integrationtime = ""; // ("INTEGRATIONTIME");
					String integrationlevel = ""; // ("INTEGRATIONLEVEL");
					String earthellipsoid = ""; // ("EARTHELLIPSOID");
					String zoneno = ""; // ("ZONENO");
					String resamplingkernel = ""; // ("RESAMPLINGKERNEL");
					String heightmode = ""; // ("HEIGHTMODE");
					String ephemerisdata = ""; // ("EPHEMERISDATA");
					String attitudedata = ""; // ("ATTITUDEDATA");
					String radiometricmethod = "";
					String mtfcorrection = ""; // ("MTFCORRECTION");
					String denoise = ""; // ("DENOISE");
					String rayleighcorrection = "";
					String usedgcpno = ""; // ("USEDGCPNO");
					String centerlatitude = ""; // ("CENTERLATITUDE");
					String centerlongitude = ""; // ("CENTERLONGITUDE");
					String topleftlatitude = ""; // ("TOPLEFTLATITUDE");
					String topleftlongitude = ""; // ("TOPLEFTLONGITUDE");
					String toprightlatitude = ""; // ("TOPRIGHTLATITUDE");
					String toprightlongitude = "";
					String bottomrightlatitude = "";
					String bottomrightlongitude = "";
					String bottomleftlatitude = "";
					String bottomleftlongitude = "";
					String topleftmapx = "";
					String topleftmapy = "";
					String toprightmapx = "";
					String toprightmapy = "";
					String bottomrightmapx = "";
					String bottomrightmapy = "";
					String bottomleftmapx = "";
					String bottomleftmapy = "";
					String orbitid = "";
					String overalldataquality = "";
					String thumbfilelocation = "";
					if (level.equalsIgnoreCase("3")||level.equalsIgnoreCase("4")||level.equalsIgnoreCase("5")) {
						productid = rs.getString("PRODUCTID");
						productdate = rs.getString("PRODUCTDATE");
						mapprojection = rs.getString("MAPPROJECTION");
						producttype = rs.getString("PRODUCTTYPE");
						//usertype = rs.getString("USERTYPE");
						imagegsd = rs.getString("IMAGEGSD");
						earthmodel = rs.getString("EARTHMODEL");
						sensorid = rs.getString("SENSORID");
						satelliteid = rs.getString("SATELLITEID");
						productlevel = rs.getString("PRODUCTLEVEL");
						scenedate = rs.getString("SCENEDATE");
						scenepath = rs.getString("SCENEPATH");
						scenerow = rs.getString("SCENEROW");
						centerlatitude = rs.getString("CENTERLATITUDE");
						centerlongitude = rs.getString("CENTERLONGITUDE");
						topleftlatitude = rs.getString("TOPLEFTLATITUDE");
						topleftlongitude = rs.getString("TOPLEFTLONGITUDE");
						toprightlatitude = rs.getString("TOPRIGHTLATITUDE");
						toprightlongitude = rs.getString("TOPRIGHTLONGITUDE");
						bottomrightlatitude = rs
								.getString("BOTTOMRIGHTLATITUDE");
						bottomrightlongitude = rs
								.getString("BOTTOMRIGHTLONGITUDE");
						bottomleftlatitude = rs.getString("BOTTOMLEFTLATITUDE");
						bottomleftlongitude = rs
								.getString("BOTTOMLEFTLONGITUDE");
						bandnumber = rs.getString("BANDNUMBER");
						//recorddate = rs.getString("RECORDDATE");
						overalldataquality = rs.getString("OVERALLDATAQUALITY");
						thumbfilelocation= rs.getString("BROWSEFILELOCATION");
						receivestationid = rs.getString("RECEIVESTATIONID");

					} else {

						productid = rs.getString("PRODUCTID");
						sceneid = rs.getString("SCENEID");
						productdate = rs.getString("PRODUCTDATE");
						mapprojection = rs.getString("MAPPROJECTION");
						producttype = rs.getString("PRODUCTTYPE");
						earthmodel = rs.getString("EARTHMODEL");
						sensorid = rs.getString("SENSORID");
						satelliteid = rs.getString("SATELLITEID");
						receivestationid = rs.getString("RECEIVESTATIONID");
						receivetime = rs.getString("RECEIVETIME");
						orbittype = rs.getString("ORBITTYPE");
						atttype = rs.getString("ATTTYPE");
						stripid = rs.getString("STRIPID");
						producetype = rs.getString("PRODUCETYPE");
						productlevel = rs.getString("PRODUCTLEVEL");
						productformat = rs.getString("PRODUCTFORMAT");
						scenedate = rs.getString("SCENEDATE");
						bandnumber = rs.getString("BANDNUMBER");
						scenepath = rs.getString("SCENEPATH");
						scenerow = rs.getString("SCENEROW");
						satpath = rs.getString("SATPATH");
						satrow = rs.getString("SATROW");
						scenecount = rs.getString("SCENECOUNT");
						sceneshift = rs.getString("SCENESHIFT");
						starttime = rs.getString("STARTTIME");
						endtime = rs.getString("ENDTIME");
						centertime = rs.getString("CENTERTIME");
						startline = rs.getString("STARTLINE");
						endline = rs.getString("ENDLINE");
						imagegsd = rs.getString("IMAGEGSD");
						widthinpixels = rs.getString("WIDTHINPIXELS");
						heightinpixels = rs.getString("HEIGHTINPIXELS");
						widthinmeters = rs.getString("WIDTHINMETERS");
						heightinmeters = rs.getString("HEIGHTINMETERS");
						regionname = rs.getString("REGIONNAME");
						cloudpercent = rs.getString("CLOUDPERCENT");
						datasize = rs.getString("DATASIZE");
						rollviewingangle = rs.getString("ROLLVIEWINGANGLE");
						pitchviewingangle = rs.getString("PITCHVIEWINGANGLE");
						rollsatelliteangle = rs.getString("ROLLSATELLITEANGLE");
						pitchsatelliteangle = rs
								.getString("PITCHSATELLITEANGLE");
						yawsatelliteangle = rs.getString("YAWSATELLITEANGLE");
						solarazimuth = rs.getString("SOLARAZIMUTH");
						solarzenith = rs.getString("SOLARZENITH");
						satelliteazimuth = rs.getString("SATELLITEAZIMUTH");
						satellitezenith = rs.getString("SATELLITEZENITH");
						gainmode = rs.getString("GAINMODE");
						integrationtime = rs.getString("INTEGRATIONTIME");
						integrationlevel = rs.getString("INTEGRATIONLEVEL");
						earthellipsoid = rs.getString("EARTHELLIPSOID");
						zoneno = rs.getString("ZONENO");
						resamplingkernel = rs.getString("RESAMPLINGKERNEL");
						heightmode = rs.getString("HEIGHTMODE");
						ephemerisdata = rs.getString("EPHEMERISDATA");
						attitudedata = rs.getString("ATTITUDEDATA");
						radiometricmethod = rs.getString("RADIOMETRICMETHOD");
						mtfcorrection = rs.getString("MTFCORRECTION");
						denoise = rs.getString("DENOISE");
						rayleighcorrection = rs.getString("RAYLEIGHCORRECTION");
						usedgcpno = rs.getString("USEDGCPNO");
						centerlatitude = rs.getString("CENTERLATITUDE");
						centerlongitude = rs.getString("CENTERLONGITUDE");
						topleftlatitude = rs.getString("TOPLEFTLATITUDE");
						topleftlongitude = rs.getString("TOPLEFTLONGITUDE");
						toprightlatitude = rs.getString("TOPRIGHTLATITUDE");
						toprightlongitude = rs.getString("TOPRIGHTLONGITUDE");
						bottomrightlatitude = rs
								.getString("BOTTOMRIGHTLATITUDE");
						bottomrightlongitude = rs
								.getString("BOTTOMRIGHTLONGITUDE");
						bottomleftlatitude = rs.getString("BOTTOMLEFTLATITUDE");
						bottomleftlongitude = rs
								.getString("BOTTOMLEFTLONGITUDE");
						topleftmapx = rs.getString("TOPLEFTMAPX");
						topleftmapy = rs.getString("TOPLEFTMAPY");
						toprightmapx = rs.getString("TOPRIGHTMAPX");
						toprightmapy = rs.getString("TOPRIGHTMAPY");
						bottomrightmapx = rs.getString("BOTTOMRIGHTMAPX");
						bottomrightmapy = rs.getString("BOTTOMRIGHTMAPY");
						bottomleftmapx = rs.getString("BOTTOMLEFTMAPX");
						bottomleftmapy = rs.getString("BOTTOMLEFTMAPY");
						orbitid = rs.getString("ORBITID");
						overalldataquality = rs.getString("OVERALLDATAQUALITY");
						thumbfilelocation = rs.getString("THUMBFILELOCATION");

					}
					image.setProductlevel(productlevel);
					image.setProductdate(productdate);
					image.setScenepath(scenepath);
					image.setScenerow(scenerow);
					image.setCenterlatitude(centerlatitude);
					image.setCenterlongitude(centerlongitude);
					image.setBand(bandnumber);
					image.setThumbfilelocation(thumbfilelocation);
					image.setScenedate(scenedate);
					image.setReceive(receivestationid);
					image.setTopleftlatitude(Double.valueOf(topleftlatitude));
					image.setTopleftlongitude(Double.valueOf(topleftlongitude));

					image.setToprightlatitude(Double.valueOf(toprightlatitude));
					image.setToprightlongitude(Double
							.valueOf(toprightlongitude));

					image.setBottomleftlongitude(Double
							.valueOf(bottomleftlongitude));
					image.setBottomleftlatitude(Double
							.valueOf(bottomleftlatitude));
					image.setBottomrightlongitude(Double
							.valueOf(bottomrightlongitude));
					image.setBottomrightlatitude(Double
							.valueOf(bottomrightlatitude));

					image.setProductid(BigDecimal.valueOf(Long
							.valueOf(productid)));
					image.setSatelliteid(satelliteid);
					image.setSensorid(sensorid);
					image.setSceneid(sceneid);
					image.setProductdate(productdate);
					image.setMapprojection(mapprojection);
					image.setProducttype(producttype);
					image.setEarthmodel(earthmodel);
					image.setReceivetime(receivetime);
					image.setOrbittype(orbittype);
					image.setAtttype(atttype);
					image.setStripid(stripid);
					image.setProducetype(producetype);
					image.setProductlevel(productlevel);
					image.setProductformat(productformat);
					image.setBandnumber(bandnumber);
					image.setScenepath(scenepath);
					image.setScenerow(scenerow);
					image.setSatpath(satpath);
					image.setSatrow(satrow);
					image.setScenecount(scenecount);
					image.setSceneshift(sceneshift);
					image.setStarttime(starttime);
					image.setEndtime(endtime);
					image.setCentertime(centertime);
					image.setStartline(startline);
					image.setEndline(endline);
					image.setImagegsd(imagegsd);
					image.setWidthinpixels(widthinpixels);
					image.setHeightinpixels(heightinpixels);
					image.setWidthinmeters(widthinmeters);
					image.setHeightinmeters(heightinmeters);
					image.setRegionname(regionname);
					image.setCloudpercent(cloudpercent);
					image.setDatasize(datasize);
					image.setRollviewingangle(rollviewingangle);
					image.setPitchviewingangle(pitchviewingangle);
					image.setRollsatelliteangle(rollsatelliteangle);
					image.setPitchsatelliteangle(pitchsatelliteangle);
					image.setYawsatelliteangle(yawsatelliteangle);
					image.setSolarazimuth(solarazimuth);
					image.setSolarzenith(solarzenith);
					image.setSatelliteazimuth(satelliteazimuth);
					image.setSatellitezenith(satellitezenith);
					image.setGainmode(gainmode);
					image.setIntegrationtime(integrationtime);
					image.setIntegrationlevel(integrationlevel);
					image.setEarthellipsoid(earthellipsoid);
					image.setZoneno(zoneno);
					image.setResamplingkernel(resamplingkernel);
					image.setHeightmode(heightmode);
					image.setEphemerisdata(ephemerisdata);
					image.setAttitudedata(attitudedata);
					image.setRadiometricmethod(radiometricmethod);
					image.setMtfcorrection(mtfcorrection);
					image.setDenoise(denoise);
					image.setRayleighcorrection(rayleighcorrection);
					image.setUsedgcpno(usedgcpno);
					image.setCenterlatitude(centerlatitude);
					image.setCenterlongitude(centerlongitude);
					image.setTopleftmapx(topleftmapx);
					image.setTopleftmapy(topleftmapy);
					image.setToprightmapx(toprightmapx);
					image.setToprightmapy(toprightmapy);
					image.setBottomrightmapx(bottomrightmapx);
					image.setBottomrightmapy(bottomrightmapy);
					image.setBottomleftmapx(bottomleftmapx);
					image.setBottomleftmapy(bottomleftmapy);
					image.setOrbitid(orbitid);
					image.setOveralldataquality(overalldataquality);

				}

			}

		});
		return image;
	}

	public String getProFromCar(final String sql) {
		// TODO Auto-generated method stub
		productStr = new StringBuffer();

		this.getSessionFactory().getCurrentSession().doWork(new Work() {
			ResultSet rs = null;
			Statement stmt = null;

			public void execute(Connection connection) throws SQLException {
				// TODO Auto-generated method stub
				stmt = connection.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				System.out.println(DateTimeUtils.getNowStrTime()
						+ "-getProFromCar-" + sql);
				rs = stmt.executeQuery(sql);
				while (rs != null && rs.next()) {
					String product = rs.getString("PRODUCT");
					productStr.append(product + ",");
				}
			}
		});
		return productStr.toString();
	}

	@SuppressWarnings("unchecked")
	public ArrayList<DataQueryModel> getNational(final String sql) {
		// TODO Auto-generated method stub
		nationallist = new ArrayList();
		this.getSessionFactory().getCurrentSession().doWork(new Work() {
			ResultSet rs = null;
			Statement stmt = null;

			public void execute(Connection connection) throws SQLException {
				// TODO Auto-generated method stub
				stmt = connection.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				System.out.println(DateTimeUtils.getNowStrTime()
						+ "-getNational-" + sql);
				rs = stmt.executeQuery(sql);
				while (rs != null && rs.next()) {
					String gid = rs.getString("GID");
					String name = rs.getString("NAME");
					String en_name = rs.getString("EN_NAME");

					DataQueryModel national = new DataQueryModel();
					national.setNationalid(gid);
					national.setNationalname(name);
					national.setNationalenname(en_name);

					national.setProvinceid(gid);
					national.setProvincename(name);
					national.setProvinceenname(en_name);

					national.setCountyid(gid);
					national.setCountyname(name);
					national.setCountyenname(en_name);

					nationallist.add(national);
				}
			}
		});
		return nationallist;
	}

	public String getPolygon(final String sql) {
		// TODO Auto-generated method stub
		polygonList = "";
		this.getSessionFactory().getCurrentSession().doWork(new Work() {
			ResultSet rs = null;
			Statement stmt = null;

			public void execute(Connection connection) throws SQLException {
				// TODO Auto-generated method stub
				stmt = connection.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				System.out.println(DateTimeUtils.getNowStrTime()
						+ "-getPolygon-" + sql);
				rs = stmt.executeQuery(sql);
				while (rs != null && rs.next()) {
					Clob clob = rs.getClob("POLYGON");
					String polygon = clob != null ? clob.getSubString(1,
							(int) clob.length()) : null;
					polygonList = polygon;
				}
			}
		});
		return polygonList;
	}
}
